using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using Microsoft.Build.Utilities;
using Microsoft.Build.Framework;

namespace UDS.Build.Database
{
    public class ValidateDatabaseObjects : DatabaseBuildTaskBase
    {
        private string outputFile;

        public string Output
        {
            get
            {
                return outputFile;
            }
            set
            {
                outputFile = value;
            }
        }

        private string cmdText = @"select [name], 
CASE WHEN
	OBJECTPROPERTY(id, 'IsProcedure') = 1 THEN 'P' 
	WHEN OBJECTPROPERTY(id, 'IsScalarFunction') =1 THEN 'F'
	WHEN OBJECTPROPERTY(id, 'IsTableFunction') = 1 THEN 'F' 
	WHEN OBJECTPROPERTY(id, 'IsInlineFunction') = 1 THEN 'F'
	WHEN OBJECTPROPERTY(id, 'IsTable') = 1 THEN 'T'
END as objtype,
OBJECTPROPERTY (id, 'IsAnsiNullsOn') as AnsiNull,
OBJECTPROPERTY (id, 'IsQuotedIdentOn') as QuotedIdentifier
from sysobjects where 	
(	
	(OBJECTPROPERTY(id, 'IsProcedure') = 1 ) 
	OR ( OBJECTPROPERTY(id, 'IsScalarFunction') = 1 ) 
	OR ( OBJECTPROPERTY(id, 'IsTableFunction') = 1 ) 
	OR ( OBJECTPROPERTY(id, 'IsInlineFunction') = 1)
	OR ( OBJECTPROPERTY(id, 'IsTable') = 1)
)
AND (
	OBJECTPROPERTY (id, 'IsAnsiNullsOn') = 0
	OR OBJECTPROPERTY (id, 'IsQuotedIdentOn') = 0 
)
order by objtype";

        public override bool Execute()
        {
            Log.LogMessage(MessageImportance.High, "Validating database objects in {0} on server {1}", DatabaseName, SqlServerInstance);
            using (SqlConnection conn = MakeConnection())
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                using (SqlDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        int nameOrd = r.GetOrdinal("name");
                        int ansiNullOrd = r.GetOrdinal("AnsiNull");
                        int quotedIdentifierOrd = r.GetOrdinal("QuotedIdentifier");
                        while (r.Read())
                        {
                            if (r.GetInt32(ansiNullOrd) == 0)
                            {
                                Log.LogError(null, "DBERR1", null, r[nameOrd] + ".sql", 0, 0, 0, 0, "Object {0} has ANSI_NULL option set to OFF.", r[nameOrd]);
                            }
                            if (r.GetInt32(quotedIdentifierOrd) == 0)
                            {
                                Log.LogError(null, "DBERR2", null, r[nameOrd] + ".sql", 0, 0, 0, 0, "Object {0} has QUOTED_IDENTIFIER option set to OFF.", r[nameOrd]);
                            }
                        }
                        return false;
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(Output))
                        {
                            if (!File.Exists(Output))
                            {
                                File.Create(Output);
                            }
                            else
                            {
                                File.SetLastWriteTime(Output, DateTime.Now);
                            }
                        }
                        return true;
                    }
                }
            }
        }
    }
}
